package com.gxuwz.Market.business.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.http.util.TextUtils;
import org.springframework.stereotype.Repository;

import com.gxuwz.Market.business.bd.DBUtil;
import com.gxuwz.Market.business.entity.ServiceData;
import com.gxuwz.Market.business.entity.ServiceField;
import com.gxuwz.Market.business.entity.ServiceForm;

import util.MyTime;

@Repository("serviceDataDAO")
public class ServiceDataDAO {
	
	/**
	 * 修改数据表
	 * @param fieldOld
	 * @param fieldNew
	 * @param dataName
	 */
	public void editTaskForm(List<ServiceField> fieldOld, List<ServiceField> fieldNew, String dataName){
		Connection conn = DBUtil.getConnection();
		//总语句
		StringBuffer delSqlSet = new StringBuffer("");
		StringBuffer addSqlSet = new StringBuffer("");
		StringBuffer editSqlSet = new StringBuffer("");
		
		//拼接删除列语句
		StringBuffer delSql = new StringBuffer("ALTER TABLE ");
		delSql.append(dataName);
		delSql.append(" DROP COLUMN ");
		//拼接增加列语句
		StringBuffer addSql = new StringBuffer("ALTER TABLE ");
		addSql.append(dataName);
		addSql.append(" ADD ");
		//拼接修改语句
		StringBuffer editSql = new StringBuffer("ALTER TABLE ");
		editSql.append(dataName);
		editSql.append(" CHANGE ");
		//遍历
		int oldIndex = 0;
		int newIndex = 0;
		int isAdd = 0;
		int isEdit = 0;
		int isDel = 0;
		boolean ing = true;
		while(ing){
			if(fieldNew.get(newIndex).getValue().equals(fieldOld.get(oldIndex).getValue())){
				//两下标相等
				if(!fieldNew.get(newIndex).getValue().equals("data"+newIndex)){
					//下标不等于当前下标
					editSqlSet.append(editSql.toString()+fieldNew.get(newIndex).getValue()+" data"+newIndex+" varchar(20) ; ");
					isEdit = 1;
				}
				oldIndex++;
				newIndex++;
			}else{
				delSqlSet.append(delSql.toString()+"data"+newIndex+";");
				isDel = 1;
				oldIndex++;
			}
			if(oldIndex == fieldOld.size()){
				//旧列先遍历完,添加剩余新序列
				for(int i=newIndex;i<fieldNew.size();i++){
					addSqlSet.append(addSql.toString()+"data"+i+" varchar(20) ; ");
					isAdd = 1;
				}
				ing = false;
			}else if(newIndex == fieldNew.size()){
				//新列先遍历完，删除往后旧序列
				for(int i=oldIndex;i<fieldOld.size();i++){
					delSqlSet.append(delSql.toString()+fieldOld.get(i)+";");
					isDel = 1;
				}
				ing = false;
			}
			
		}
		try {
			if(isDel == 1){
				PreparedStatement ptmt1;
				ptmt1 = conn.prepareStatement(delSqlSet.toString());
				ptmt1.execute();
			}
			if(isEdit == 1){
				PreparedStatement ptmt2;
				ptmt2 = conn.prepareStatement(editSqlSet.toString());
				ptmt2.execute();
			}
			if(isAdd == 1){
				PreparedStatement ptmt3;
				ptmt3 = conn.prepareStatement(addSqlSet.toString());
				ptmt3.execute();
			}


		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
	/**
	 * 增加一条数据
	 * @param dataFormName
	 * @param serviceData
	 */
	public void add(String dataFormName, ServiceData serviceData){
		String[] datas = serviceData.getDatas();
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("insert into ");
		sql.append(dataFormName);
		sql.append(" ( writer_id");
		for(int i=0;i<serviceData.getDatas().length;i++){
			sql.append(" , data");
			sql.append(i);
		}
		sql.append(" ) value ( '");
		sql.append(serviceData.getWriterId());
		sql.append("'");
		for(int i=0;i<serviceData.getDatas().length;i++){
			sql.append(" , '");
			sql.append(datas[i]);
			sql.append("'");
		}
		sql.append(" )");
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 批量添加数据
	 * @param taskId
	 * @param list
	 */
	public void addDatas(String taskId, List<ServiceData> list, int fieldNum){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql1 = new StringBuffer("insert ignore into ");
		sql1.append(" service_");
		sql1.append(taskId);
		sql1.append("_data ");
		StringBuffer sql2 = new StringBuffer(" (writer_id");
		for(int i=0;i<fieldNum;i++){
			sql2.append(" ,data");
			sql2.append(i);
		}
		sql2.append(" ) values");
		
		
		
		StringBuffer sql3 = new StringBuffer(" ");
		int j=0;
		for(ServiceData serviceData:list){
			j++;
			String[] datas = serviceData.getDatas();
			sql3.append("( '");
			sql3.append(serviceData.getWriterId());
			sql3.append("'");
			for(String dataValue:datas){
				sql3.append(",'");
				sql3.append(dataValue);
				sql3.append("'");
			}
			
			if(j == list.size()){
				sql3.append(" );");
			}else{
				sql3.append("),");
			}
			
		}
		sql1.append(sql2);
		sql1.append(sql3);
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql1.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	/**
	 * 删除一条数据
	 * @param dataFormName
	 * @param id
	 */
	public void delete(String dataFormName, int id){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("delete from ");
		sql.append(dataFormName);
		sql.append(" where id=");
		sql.append(id);

		try{
			PreparedStatement ptmt=conn.prepareStatement(sql.toString());
			ptmt.execute();
		}catch(Exception e){
			System.out.println(e);
		}
		
	}
	
	/**
	 * 更新一条数据
	 * @param dataFormName
	 * @param serviceData
	 */
	public void update(String dataFormName, ServiceData serviceData){
		String[] datas = serviceData.getDatas();
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("update ");
		sql.append(dataFormName);
		sql.append(" set data_state=1");
		for(int i=0;i<serviceData.getDatas().length;i++){
			sql.append(", data");
			sql.append(i);
			sql.append(" = '");
			sql.append(datas[i]);
			sql.append("' ");
		}
		sql.append(" where id=");
		sql.append(serviceData.getId());
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
	}
	/**
	 * 查询是否填写人
	 * @param dataFormName
	 * @param writerId
	 * @return
	 */
	public boolean isWriter(String dataFormName, String writerId){
		Connection conn=DBUtil.getConnection();
		StringBuilder sb=new StringBuilder("select * from ");
		sb.append(dataFormName);
		sb.append(" where writer_id='");
		sb.append(writerId);
		sb.append("' ");
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sb.toString());
			ResultSet rs=ptmt.executeQuery();
			if(rs.next()){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return false;
	}
	/**
	 * 根据用户id查询用户信息
	 * @param dataFormName
	 * @param writerId
	 * @param fieldNum
	 * @return
	 */
	public ServiceData findByWriterId(String dataFormName, String writerId, int fieldNum){
		//数据实体
		ServiceData serviceData = new ServiceData();
		Connection conn=DBUtil.getConnection();
		StringBuilder sb=new StringBuilder("select * from ");
		sb.append(dataFormName);
		sb.append(" where writer_id='");
		sb.append(writerId);
		sb.append("' ");
		PreparedStatement ptmt;
		try{
			ptmt = conn.prepareStatement(sb.toString());
			ResultSet rs=ptmt.executeQuery();
			if(rs.next()){
				serviceData.setId(rs.getInt("id"));
				serviceData.setDataState(rs.getInt("data_state"));
		
		//		data.setdataId(rs.getString("data_id"));
		//		data.setReviseNum(rs.getInt("revise_num"));
				serviceData.setWriterId(rs.getString("writer_id"));
				
				//给数据赋值
				if(fieldNum > 0){
					String[] ds = new String[fieldNum];
					for(int i=0;i<fieldNum;i++){
						ds[i] = rs.getString("data"+i);
					}
					serviceData.setDatas(ds);
				}
				
			}else{
				//若是为查找到数据返回null
				return null;
			}
			
			
		}catch(Exception e){
			System.out.println(e);
		}
		
		
		return serviceData;
		
	}
	
	
	/**
	 * 查找对应表所有数据
	 * @param dataFormName
	 * @param fieldNum
	 * @return
	 */
	public List<ServiceData> findAll(String dataFormName, int fieldNum){
		List<ServiceData> dataList=new ArrayList<ServiceData>();
		
		Connection conn=DBUtil.getConnection();
		StringBuilder sb=new StringBuilder("select * from ");
		sb.append(dataFormName);
		
		try{
			PreparedStatement ptmt=conn.prepareStatement(sb.toString());
			
			ResultSet rs=ptmt.executeQuery();
			
			ServiceData data=null;
			while(rs.next()){
				data = new ServiceData();
				data.setId(rs.getInt("id"));
		//		data.setdataId(rs.getString("data_id"));
		//		data.setReviseNum(rs.getInt("revise_num"));
				data.setWriterId(rs.getString("writer_id"));
				String[] ds = new String[fieldNum];
				for(int i=0;i<fieldNum;i++){
					ds[i] = rs.getString("data"+i);
				}
				data.setDatas(ds);
				dataList.add(data);
			}
		}catch(Exception e){
			System.out.println(e);
		}
		
		
		return dataList;
		
		
	}
	/**
	 * 根据条件模糊查询
	 * @param dataFormName
	 * @param fieldNum
	 * @param serviceData
	 * @return
	 */
	public List<ServiceData> find(String dataFormName, int fieldNum, ServiceData serviceData){
		List<ServiceData> dataList=new ArrayList<ServiceData>();
		
		Connection conn=DBUtil.getConnection();
		StringBuilder sb=new StringBuilder("select * from ");
		sb.append(dataFormName);
		sb.append(" where 1=1 ");
		if(serviceData != null){
			//模糊查询部分
			if(serviceData.getDataId()!=null && !"".equals(serviceData.getDataId())){
				sb.append(" and data_id like '%");
				sb.append(serviceData.getDataId());
				sb.append("%' ");
			}
			if(serviceData.getWriterId()!=null && !"".equals(serviceData.getWriterId())){
				sb.append(" and writer_id like '%");
				sb.append(serviceData.getWriterId());
				sb.append("%' ");
			}
			if(serviceData.getDataState()!=null && !"".equals(serviceData.getDataState())){
				sb.append(" and data_state = ");
				sb.append(serviceData.getDataState());
			}
			if(serviceData.getDatas() != null){
				String[] ds = serviceData.getDatas();//可能出了问题
				
				if(null !=ds && !ds.equals("") && ds.length>0){
					for(int i=0;i<fieldNum;i++){
						if(ds[i]!=null && !"".equals(ds[i])){
							sb.append(" and data"
									+ i
									+ " like '%");
							sb.append(ds[i]);
							sb.append("%' ");
						}
					}
				}
			}
		}
		try{
			PreparedStatement ptmt=conn.prepareStatement(sb.toString());
			
			ResultSet rs=ptmt.executeQuery();
			
			ServiceData data=null;
			
			while(rs.next()){
				data = new ServiceData();
				data.setId(rs.getInt("id"));
				data.setDataState(rs.getInt("data_state"));
	//			data.setdataId(rs.getString("data_id"));
	//			data.setReviseNum(rs.getInt("revise_num"));
				data.setWriterId(rs.getString("writer_id"));
				String[] das = new String[fieldNum];
				for(int i=0;i<fieldNum;i++){
					das[i] = rs.getString("data"+i);
				}
				data.setDatas(das);
				dataList.add(data);
			}
		}catch(Exception e){
			System.out.println(e);
		}
		return dataList;
	}
	
	
	/**
	 * 创建表
	 * @param taskId
	 * @param serviceForm
	 */
	public void createDB(String taskId, ServiceForm serviceForm){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("create table");
		sql.append(" service_");
		sql.append(taskId);
		sql.append("_data");
		sql.append(" ( id INT(11) not null AUTO_INCREMENT,"
				+ " data_state int(3) default 0,"
				+ " writer_id varchar(20) ,"
				+ " update_time int(10) ,");
		String[] fieldLen = serviceForm.getFieldLen().split(",");
		for(int i = serviceForm.getFieldNum()-1;i>=0;i--){
			sql.append("data");
			sql.append(i);
			sql.append(" varchar(");
			sql.append(fieldLen[i]);
			sql.append(") , ");
		}
		sql.append("unique (writer_id) , primary key (id) ) ");
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	/**
	 * 添加填写者
	 * @param taskId
	 * @param list
	 */
	public void addWriter(String taskId, List<String> list){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("insert ignore into ");
		sql.append(" service_");
		sql.append(taskId);
		sql.append("_data ");
		sql.append(" (writer_id) ");
		
		for(int i=0;i<list.size();i++){
			sql.append(" select '"
					+ list.get(i)
					+ "' ");
			if(i<list.size()-1){
				sql.append(" union all ");
			}
		}
		PreparedStatement ptmt;
		try {
			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	
	/**
	 * 查询完成状态
	 * @param dataName
	 * @return
	 */
	public String finish(String dataName){
		Connection conn = DBUtil.getConnection();
		int sum = 0;
		int num = 0;
		StringBuffer sql = new StringBuffer("select count(*) from ");
		sql.append(dataName);
		try{
			PreparedStatement ptmt=conn.prepareStatement(sql.toString());
			ResultSet rs=ptmt.executeQuery();
			while(rs.next()){
				sum = rs.getInt("count(*)");
			}
			sql.append(" where data_state = 1");
			ptmt=conn.prepareStatement(sql.toString());
			rs=ptmt.executeQuery();
			while(rs.next()){
				num = rs.getInt("count(*)");
			}
		}catch(Exception e){
			System.out.println(e);
		}
		
		return num+"/"+sum;
	}
	
	public void copy(String newName, String oldName){
		Connection conn = DBUtil.getConnection();
		StringBuffer sql = new StringBuffer("CREATE TABLE ");
		sql.append(newName);
		sql.append(" AS SELECT * FROM ");
		sql.append(oldName);

		StringBuffer sqlUpdate = new StringBuffer("UPDATE ");
		sqlUpdate.append(newName);
		sqlUpdate.append(" SET data_state = 0 ;");
		PreparedStatement ptmt;
		PreparedStatement ptmt2;
		try {

			ptmt = conn.prepareStatement(sql.toString());
			ptmt.execute();
			ptmt2 = conn.prepareStatement(sqlUpdate.toString());
			ptmt2.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获得用户邮箱
	 * @param ids
	 * @return
	 */
	public String sendMail(String ids){
		StringBuffer emails = new StringBuffer();
		Connection conn=DBUtil.getConnection();
		StringBuilder sb=new StringBuilder("select * from sys_user where user_id in(");
		sb.append(ids);
		sb.append(")");
		
		try{
			PreparedStatement ptmt=conn.prepareStatement(sb.toString());
			
			ResultSet rs=ptmt.executeQuery();
			
			while(rs.next()){
				if(!TextUtils.isEmpty(rs.getString("user_email"))){
					emails.append(rs.getString("user_email"));
					emails.append(",");
				}
				

			}
		}catch(Exception e){
			System.out.println(e);
		}
		
		
		return emails.toString();
		
		
	}
}
